package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.heaven.entity.test.StudentTest;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 学生作业Dao类
 * Created by chenxin on 2015/3/20.
 */
@Repository
public class StudentTestDao extends BaseDao {
    /**
     * 查询学生保存的线下作业文件
     * 
     * @param testId 作业ID
     * @param studentId 学生ID
     * @return StudentTest
     * 
     * @author cx
     * @since 2015/3/20
     */
    public StudentTest getSavedTest(final long testId, final long studentId) {
        String sql = "SELECT sSubmittedFileName,sSubmittedDownPath,sSubmittedShowPath FROM t_student_test " +
                "WHERE iTestId=:testId AND iStudentId=:studentId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("testId", testId).addValue("studentId", studentId);

        try {
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<StudentTest>() {
                @Override
                public StudentTest mapRow(ResultSet rs, int rowNum) throws SQLException {
                    StudentTest studentTest = new StudentTest();

                    studentTest.setSubmittedFileName(rs.getString("sSubmittedFileName"));
                    studentTest.setSubmittedDownPath(rs.getString("sSubmittedDownPath"));
                    studentTest.setSubmittedShowPath(rs.getString("sSubmittedShowPath"));

                    return studentTest;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            return null;
        }
    }

    /**
     * 保存上传作业的信息
     *
     * @param studentTest 学生作业
     * @return int
     * 
     * @author cx
     * @since 2015/3/20
     */
    public int saveOrUpdateFilePath(final StudentTest studentTest) {
        String select = "SELECT COUNT(id) FROM t_student_test WHERE iStudentId=:studentId AND iTestId=:testId";
        String sql;

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("studentId", studentTest.getStudentId()).addValue("testId", studentTest.getTestId());

        //查询学生作业是否已存在
        int result = namedParameterJdbcTemplate.queryForObject(select, sps, Integer.class);

        if (result < 1) { //新增学生作业记录
            sql = "INSERT INTO t_student_test(iStudentId,iTestId,sSubmittedFileName,sSubmittedDownPath) " +
                    "VALUES(:studentId,:testId,:fileName,:filePath)";
        }
        else { //更新学生作业信息，限制为未提交的状态
            sql = "UPDATE t_student_test SET sSubmittedFileName=:fileName,sSubmittedDownPath=:filePath WHERE iStudentId=:studentId" +
                    " AND iTestId=:testId AND tiStatus=0";
        }

        sps.addValue("fileName", studentTest.getSubmittedFileName()).addValue("filePath", studentTest.getSubmittedDownPath());
        
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 删除学生线下作业（未提交）
     * 
     * @param studentId 学生ID
     * @param testId 作业ID
     * @return int
     * 
     * @author cx
     * @since 2015/3/21
     */
    public int deleteTestFile(final long studentId, final long testId) {
        String sql = "DELETE FROM t_student_test WHERE iStudentId=:studentId AND iTestId=:testId AND tiStatus=0";
        
        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("studentId", studentId)
                .addValue("testId", testId);

        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 更新作业为提交状态
     *
     * @param studentId 学生ID
     * @param testId 作业ID
     * @return int
     *
     * @author cx
     * @since 2015/3/21
     */
    public int updateTestSubmitted(final long studentId, final long testId) {
        //更新前要求作业状态为未提交
        String sql = "UPDATE t_student_test SET tiStatus=1 WHERE iStudentId=:studentId AND iTestId=:testId AND tiStatus=0";

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("studentId", studentId)
                .addValue("testId", testId);

        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 查询学生作业最后的答题位置
     * 
     * @param studentId 学生ID
     * @param testId 作业测试ID
     * @return 习题ID
     * 
     * @author cx
     * @since 2015/3/23
     */
    public long getLastPosition(final long studentId, final long testId) {
        String sql = "SELECT iLastPosition FROM t_student_test WHERE iStudentId=:studentId AND iTestId=:testId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("studentId", studentId).addValue("testId", testId);

        try {
            return namedParameterJdbcTemplate.queryForObject(sql, sps, Long.class);
        } catch (EmptyResultDataAccessException ex) {
            return 0;
        }
    }

    /**
     * 返回指定作业的提交学生及批阅状态
     * @param testId 作业编号
     * @return List
     *
     * @author fanruiping
     * @since 2015-03-31
     */
    public List<StudentTest> getStudentsByTest(Long testId){
        String sql = "SELECT iStudentId,siScore,tiReadOver FROM t_student_test WHERE iTestId=:testId AND tiStatus=1 ORDER BY siScore DESC";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("testId", testId), new RowMapper<StudentTest>() {
            @Override
            public StudentTest mapRow(ResultSet resultSet, int i) throws SQLException {
                StudentTest student = new StudentTest();

                student.setStudentId(resultSet.getLong("iStudentId"));
                student.setScore(resultSet.getInt("siScore"));
                student.setReadOver(resultSet.getInt("tiReadOver"));

                return student;
            }
        });
    }

    /**
     * 获取指定作业的提交人数
     * @param testId 作业编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-01
     */
    public int getSubmittedCnt(Long testId){
        String sql = "SELECT COUNT(id) FROM t_student_test WHERE iTestId=:testId";

        return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("testId", testId), Integer.class);
    }

    /**
     * 获取所有提交该作业的学生编号列表
     * @param testId 作业编号
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-01
     */
    public List<Long> getStudentIds(Long testId){
        String sql = "SELECT iStudentId FROM t_student_test WHERE iTestId=:testId";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("testId", testId), new RowMapper<Long>() {
            @Override
            public Long mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getLong("iStudentId");
            }
        });
    }

    /**
     * 获取指定学生完成指定作业的情况
     * @param testId 作业编号
     * @param studentId 学生编号
     * @return StudentTest
     *
     * @author fanruiping
     * @since 2015-04-02
     */
    public StudentTest getDetailByStudentId(Long testId, final Long studentId){
        try{
            String sql = "SELECT id,siScore,sSubmittedFileName,sSubmittedDownPath,sSubmittedShowPath,sCorrectedFileName," +
                    "sCorrectedDownPath,sCorrectedShowPath FROM t_student_test WHERE iTestId=:testId AND iStudentId=:studentId";

            MapSqlParameterSource param = new MapSqlParameterSource();
            param.addValue("testId",testId);
            param.addValue("studentId",studentId);

            return namedParameterJdbcTemplate.queryForObject(sql, param, new RowMapper<StudentTest>() {
                @Override
                public StudentTest mapRow(ResultSet resultSet, int i) throws SQLException {
                    StudentTest studentTest = new StudentTest();

                    studentTest.setId(resultSet.getLong("id"));
                    studentTest.setScore(resultSet.getInt("siScore"));
                    studentTest.setSubmittedFileName(resultSet.getString("sSubmittedFileName"));
                    studentTest.setSubmittedDownPath(resultSet.getString("sSubmittedDownPath"));
                    studentTest.setSubmittedShowPath(resultSet.getString("sSubmittedShowPath"));
                    studentTest.setCorrectedFileName(resultSet.getString("sCorrectedFileName"));
                    studentTest.setCorrectedDownPath(resultSet.getString("sCorrectedDownPath"));
                    studentTest.setCorrectShowPath(resultSet.getString("sCorrectedShowPath"));

                    return studentTest;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("未获取到指定学生的指定作业详细:EmptyResultDataAccessException ex");

            return null;
        }
    }

    /**
     * 批阅线下作业
     * @param studentTest 学生作业实体
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-02
     */
    public int readOverOffline(StudentTest studentTest){
        String sql = "UPDATE t_student_test SET siScore=:score,tiReadOver=1 WHERE iTestId=:testId AND iStudentId=:studentId";

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("score",studentTest.getScore());
        param.addValue("testId",studentTest.getTestId());
        param.addValue("studentId",studentTest.getStudentId());

        return namedParameterJdbcTemplate.update(sql,param);
    }

    /**
     * 线下作业是否已批阅完成
     * @param testId 作业编号
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-04-20
     */
    public boolean offlineHasReadOver(Long testId){
        try {
            String sql = "SELECT id FROM t_student_test WHERE iTestId=:testId AND tiReadOver=0 AND tiStatus=1 LIMIT 1";

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("testId",testId), Long.class) > 0;
        }catch (EmptyResultDataAccessException ex){
            return true;
        }
    }

    /**
     * 更新批阅文件信息
     * @param testId 作业编号
     * @param studentId 学生编号
     * @param correctFileName 当前文件名
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public int updateReadOverFile(Long testId,Long studentId,String correctFileName,String fileUrl){
        String sql = "UPDATE t_student_test SET sCorrectedDownPath=:downPath,sCorrectedFileName=:fileName " +
                "WHERE iTestId=:testId AND iStudentId=:studentId";

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("downPath",fileUrl);
        param.addValue("fileName", correctFileName);
        param.addValue("testId",testId);
        param.addValue("studentId",studentId);

        return namedParameterJdbcTemplate.update(sql,param);
    }

    /**
     * 删除批阅文件
     * @param testId 作业编号
     * @param studentId 学生编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public int deleteReadOverFile(Long testId,Long studentId){
        String sql = "UPDATE t_student_test SET sCorrectedShowPath='',sCorrectedDownPath='',sCorrectedFileName='' " +
                "WHERE iTestId=:testId AND iStudentId=:studentId";

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("testId",testId);
        param.addValue("studentId",studentId);

        return namedParameterJdbcTemplate.update(sql,param);
    }

    /**
     * 更新学生作业分值
     * @param studentId 学生编号
     * @param testId 作业编号
     * @param score 分值
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-03
     */
    public int updateScore(Long studentId,Long testId,int score){
        String sql = "UPDATE t_student_test SET siScore=siScore+:score WHERE iStudentId=:studentId AND iTestId=:testId";

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("studentId", studentId);
        param.addValue("testId", testId);
        param.addValue("score", score);

        return namedParameterJdbcTemplate.update(sql,param);
    }

    /**
     * 设置线上作业批阅状态为已批阅
     * @param studentId 学生编号
     * @param testId 作业编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-17
     */
    public int setOnlineReadOver(Long studentId,Long testId){
        String sql = "UPDATE t_student_test SET tiReadOver=1 WHERE iStudentId=:studentId AND iTestId=:testId";

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("studentId", studentId);
        param.addValue("testId", testId);

        return namedParameterJdbcTemplate.update(sql,param);
    }

    /**
     * 递减线上学生作业的待批阅主观题数量
     * @param studentId 学生编号
     * @param testId 作业编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-13
     */
    public int decrSubjectiveCount(Long studentId, Long testId){
        String sql = "UPDATE t_student_test SET siSubjective=siSubjective-1 WHERE iTestId=:testId AND iStudentId=:studentId";

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("testId", testId);
        param.addValue("studentId", studentId);

        return namedParameterJdbcTemplate.update(sql,param);
    }

    /**
     * 获取指定学生的指定作业的需要批阅的主观题数量
     * @param studentId 学生编号
     * @param testId 作业编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-14
     */
    public int getSubjectiveCount(Long studentId, Long testId){
        try{
            String sql = "SELECT siSubjective FROM t_student_test WHERE iTestId=:testId AND iStudentId=:studentId";

            MapSqlParameterSource param = new MapSqlParameterSource();
            param.addValue("testId", testId);
            param.addValue("studentId", studentId);

            return namedParameterJdbcTemplate.queryForObject(sql, param, Integer.class);
        }catch (EmptyResultDataAccessException ex){
            return 0;
        }
    }

    /**
     * 获取指定作业的需要批阅的主观题数量
     * @param testId 作业编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-14
     */
    public int getSubjectiveCount(Long testId){
        try{
            String sql = "SELECT SUM(siSubjective) FROM t_student_test WHERE iTestId=:testId";

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("testId", testId), Integer.class);
        }catch (EmptyResultDataAccessException ex){
            return 0;
        }
    }
}
